import datetime
import json
import traceback
from typing import List

import aiomysql
from fastapi import APIRouter
from re_common.baselibrary.database.mysql import Mysql
from re_common.baselibrary.utils.core.requests_core import MsgCode

from apps.core.m_route import ContextIncludedRoute
from apps.core.return_info import ReturnInfo, SUCCESS, FAILED
from apps.crawler_platform.core_api.models import AddSqlModel, UpdateSqlModel, DeleteSqlModel, QuerySqlModel, \
    AddManyModel, ExecSqlModel, ExecManySqlModel, UpdateSqlModelCtrl, UpdateSqlListModel, DownReportFile, \
    UpdateReportFile
from apps.crawler_platform.core_platform.core_sql import CoreSqlMixin
from apps.crawler_platform.core_platform.g_model import InputPlatformModel, CnkiMarkTable, QkUpdateManger, \
    SaveManageTaskSettingModel, FlowAirActive
from apps.crawler_platform.util.sqlhelper import SQLHelper

router = APIRouter(route_class=ContextIncludedRoute)
from warnings import filterwarnings

filterwarnings('ignore', category=aiomysql.Warning)


def update_dicts(dicts):
    strings = ""
    lists = []
    for k, v in dicts.items():
        strings = strings + k + "=%s" + ","
        lists.append(v)
    strings = strings.rstrip(',')
    return strings, lists


def update_dicts2(dicts):
    """
    不适用%s占位符号的预计 比如 stat=stat+1 这样的加一操作
    :param dicts:
    :return:
    """
    strings = ""
    for k, v in dicts.items():
        strings = strings + k + "=" + f"{v},"
    strings = strings.rstrip(',')
    return strings


def where_dicts(items):
    if isinstance(items, dict):
        strings = ""
        lists = []
        for k, v in items.items():
            strings = strings + k + "=%s " + "and "
            lists.append(v)
        strings = strings.rstrip('and ')
        return strings, lists
    else:
        strings = ""
        lists = []
        description = ""
        for item in items:
            operator = item.operator
            k = item.key
            v = item.value
            description = item.description
            strings = strings + k + operator + "%s " + description + " "
            lists.append(v)
        strings = strings.rstrip(description + ' ')
        return strings, lists


def order_dicts(dicts):
    strings = ""
    for k, v in dicts.items():
        v = Mysql.escape(v)
        if v == "RAND":
            strings = strings + "RAND(),"
        else:
            strings = strings + k + f" {v},"
    strings = strings.rstrip(',')
    return strings


@router.post("/sql_api/query_sql_raw_sql")
async def exec_sql(inputs: InputPlatformModel[ExecSqlModel]):
    """
   查询sql 直接传入sql语句
   field:*或者rawid,stat,uptime或者count(*)
   order:{"rawid":"desc","rand":"RAND","uptime":"ASC"}
   limit:0
   :return:
   """
    return_info = ReturnInfo()
    sql = ""
    try:
        sql = inputs.data.sql
        is_dicts = inputs.data.is_dicts
        cursors = ()
        if is_dicts:
            cursors = (aiomysql.DictCursor,)
        row = await SQLHelper.fetchall(sql, None, *cursors)
        return_info.status = SUCCESS
        return_info.msg_code = MsgCode.SUCCESS_CODE
        return_info.msg = "执行query sql成功;"
        return_info.data = row
        return return_info.todict()
    except:
        return_info.status = FAILED
        return_info.msg_code = 400
        return_info.msg = "执行query sql失败;" + sql
        return_info.data = traceback.format_exc()
        return return_info.todict()


@router.post("/sql_api/exec_many_sql_raw_sql")
async def exec_many_sql(inputs: InputPlatformModel[ExecManySqlModel]):
    """
   执行多条数据的sql 直接传入sql语句
   field:*或者rawid,stat,uptime或者count(*)
   order:{"rawid":"desc","rand":"RAND","uptime":"ASC"}
   limit:0
   :return:
   """
    return_info = ReturnInfo()
    sql = ""
    try:
        sql = inputs.data.sql
        value_list = inputs.data.value_list
        row = await SQLHelper.executemany_commit(sql, value_list)
        return_info.status = SUCCESS
        return_info.msg_code = MsgCode.SUCCESS_CODE
        return_info.msg = "执行query sql成功;"
        return_info.data = row
        return return_info.todict()
    except:
        return_info.status = FAILED
        return_info.msg_code = 400
        return_info.msg = "执行query sql失败;" + sql
        return_info.data = traceback.format_exc()
        return return_info.todict()


@router.post("/sql_api/exec_sql_raw_sql")
async def exec_sql(inputs: InputPlatformModel[ExecManySqlModel]):
    """
   执行多条数据的sql 直接传入sql语句
   field:*或者rawid,stat,uptime或者count(*)
   order:{"rawid":"desc","rand":"RAND","uptime":"ASC"}
   limit:0
   :return:
   """
    return_info = ReturnInfo()
    sql = ""
    try:
        sql = inputs.data.sql
        value_list = inputs.data.value_list
        row = await CoreSqlMixin.execute_commit_sql(sql, value_list)
        # row = await SQLHelper.execute_commit(sql, value_list)
        return_info.status = SUCCESS
        return_info.msg_code = MsgCode.SUCCESS_CODE
        return_info.msg = "执行query sql成功;"
        return_info.data = row
        return return_info.todict()
    except:
        return_info.status = FAILED
        return_info.msg_code = 400
        return_info.msg = "执行query sql失败;" + sql
        return_info.data = traceback.format_exc()
        return return_info.todict()


@router.post("/sql_api/query_sql")
async def query_sql(inputs: InputPlatformModel[QuerySqlModel]):
    """
   查询sql
   field:*或者rawid,stat,uptime或者count(*)
   order:{"rawid":"desc","rand":"RAND","uptime":"ASC"}
   limit:0
   :return:
   """
    return_info = ReturnInfo()
    sql = ""
    try:
        table_name = inputs.data.table
        field = inputs.data.field
        where, lists = where_dicts(inputs.data.where)
        order = order_dicts(inputs.data.order)
        limit = inputs.data.limit
        row = await CoreSqlMixin.fetchall_query(sql, lists, field, table_name, where, order, limit)
        # sql = f" SELECT {field} from {table_name} where {where} order by {order} limit {limit}"
        # row = await SQLHelper.fetchall(sql, lists, aiomysql.DictCursor)
        return_info.status = SUCCESS
        return_info.msg_code = MsgCode.SUCCESS_CODE
        return_info.msg = "执行query sql成功;"
        return_info.data = row
        return return_info.todict()
    except:
        return_info.status = FAILED
        return_info.msg_code = 400
        return_info.msg = "执行query sql失败;" + sql
        return_info.data = traceback.format_exc()
        return return_info.todict()


@router.post("/sql_api/delete_sql")
async def delete_sql(inputs: InputPlatformModel[DeleteSqlModel]):
    """
    删除sql
    :return:
    """
    return_info = ReturnInfo()
    try:
        table_name = inputs.data.table
        where, lists = where_dicts(inputs.data.where)
        sql = await CoreSqlMixin.delete(lists, table_name, where)
        # sql = f"delete from {table_name} where {where}"
        # await SQLHelper.execute_commit(sql, lists)
        return_info.status = SUCCESS
        return_info.msg_code = MsgCode.SUCCESS_CODE
        return_info.msg = "执行delete sql成功"
        return_info.data = sql
        return return_info.todict()
    except:
        return_info.status = FAILED
        return_info.msg_code = 400
        return_info.msg = "执行delete sql失败"
        return_info.data = traceback.format_exc()
        return return_info.todict()


@router.post("/sql_api/add_sql")
async def add_sql(inputs: InputPlatformModel[AddSqlModel]):
    """
    添加sql
    :return:
    """
    return_info = ReturnInfo()
    sql = ""
    try:
        table_name = inputs.data.table
        keys = ""
        list_v = []
        list_f = []
        for k, v in inputs.data.feild_dicts.items():
            keys = keys + f"`{k}`,"
            list_f.append("%s")
            list_v.append(v)

        keys = keys.rstrip(",")
        list_f = ",".join(list_f)
        insert_s = inputs.data.insert
        sql = f" {insert_s} {table_name}({keys}) values ({list_f})"
        if inputs.data.ndku_feild:
            ndku_str = ""
            for k, v in inputs.data.ndku_feild.items():
                ndku_str = ndku_str + f"`{k}`=%s,"
                list_v.append(v)
            ndku_str = ndku_str.rstrip(",")
            sql = sql + f" ON DUPLICATE KEY UPDATE {ndku_str}"
        sql = sql + ";"
        await SQLHelper.execute_commit(sql, list_v)
        return_info.status = SUCCESS
        return_info.msg_code = MsgCode.SUCCESS_CODE
        return_info.msg = "执行insert sql成功"
        return_info.data = sql
        return return_info.todict()
    except:
        return_info.status = FAILED
        return_info.msg_code = MsgCode.SQL_INSERT_ERROR
        return_info.msg = "执行insert sql失败;" + sql
        return_info.data = traceback.format_exc()
        return return_info.todict()


@router.post("/sql_api/add_many_sql")
async def add_many_sql(inputs: InputPlatformModel[AddManyModel]):
    """
    添加sql
    :return:
    """
    return_info = ReturnInfo()
    sql = ""
    value_list = []
    try:
        table_name = inputs.data.table
        keys = ",".join(inputs.data.feild_list)
        list_f = ",".join(['%s'] * len(inputs.data.feild_list))
        insert_s = inputs.data.insert
        sql = f"{insert_s} {table_name}({keys}) values ({list_f})"
        value_list = inputs.data.value_list

        new_value_list = []
        for va in value_list:
            new_value_list.append(tuple(va))

        await SQLHelper.executemany_commit(sql, new_value_list)
        return_info.status = SUCCESS
        return_info.msg_code = MsgCode.SUCCESS_CODE
        return_info.msg = "执行insert many sql成功"
        return_info.data = sql
        return return_info.todict()
    except:
        return_info.status = FAILED
        return_info.msg_code = MsgCode.SQL_INSERT_ERROR
        return_info.msg = "执行insert many sql失败;" + sql + ";" + "len is " + str(len(value_list)) + ";" + str(
            value_list[:10])
        return_info.data = traceback.format_exc()
        print(return_info.todict())
        return return_info.todict()


@router.post("/sql_api/update_sql")
async def update_sql(inputs: InputPlatformModel[UpdateSqlModel]):
    """
    添加sql
    :return:
    """
    return_info = ReturnInfo()
    sql = ""
    list_up = ""
    try:
        table_name = inputs.data.table
        up, list_up = update_dicts(inputs.data.update)
        where, lists = where_dicts(inputs.data.where)
        up_no = update_dicts2(inputs.data.update_no_placeholder)
        # # 处理failcount+1问题
        # up, list_up = solve_sql(up, list_up)
        if up_no:
            up = up + "," + up_no
        up = up.lstrip(",").rstrip(",")
        sql = f"update {table_name} set {up} where {where};"
        list_up.extend(lists)
        rows = await SQLHelper.execute_commit(sql, list_up)
        return_info.status = SUCCESS
        return_info.msg_code = MsgCode.SUCCESS_CODE
        return_info.msg = "执行update sql成功,更新影响行数" + str(rows)
        return_info.data = sql
        return return_info.todict()
    except:
        return_info.status = FAILED
        return_info.msg_code = MsgCode.SQL_UPDATE_ERROR
        return_info.msg = "执行update sql失败;" + sql + ";" + "list:" + str(list_up)
        return_info.data = traceback.format_exc()
        return return_info.todict()


@router.post("/sql_api/update_sql_list")
async def update_sql(inputs: InputPlatformModel[UpdateSqlListModel]):
    """
    添加sql
    :return:
    """
    return_info = ReturnInfo()
    sql = ""
    list_up = ""
    exc_data_list = []
    try:
        data_list = inputs.data.data_list
        for datas in data_list:
            table_name = datas.table
            up, list_up = update_dicts(datas.update)
            where, lists = where_dicts(datas.where)
            up_no = update_dicts2(datas.update_no_placeholder)
            # # 处理failcount+1问题
            # up, list_up = solve_sql(up, list_up)
            if up_no:
                up = up + "," + up_no
            up = up.lstrip(",").rstrip(",")
            sql = f"update {table_name} set {up} where {where};"
            list_up.extend(lists)
            exc_data_list.append((sql, tuple(list_up)))
        rows = await SQLHelper.executemany_list_commit(exc_data_list)
        return_info.status = SUCCESS
        return_info.msg_code = MsgCode.SUCCESS_CODE
        return_info.msg = "执行update sql成功,更新影响行数" + str(rows)
        return_info.data = sql
        return return_info.todict()
    except:
        return_info.status = FAILED
        return_info.msg_code = MsgCode.SQL_UPDATE_ERROR
        return_info.msg = "执行update sql失败;" + sql + ";" + "list:" + str(list_up)
        return_info.data = traceback.format_exc()
        return return_info.todict()


@router.post("/sql_api/update_json_sql")
async def update_json_sql(inputs: InputPlatformModel[UpdateSqlModel]):
    """
    更新json字段
    :return:
    """
    return_info = ReturnInfo()
    sql = ""
    list_up = ""
    try:
        table_name = inputs.data.table
        upinfo = inputs.data.update
        where, lists = where_dicts(inputs.data.where)
        sql = f" SELECT * from {table_name} where {where} "
        row = await SQLHelper.fetchone(sql, lists, aiomysql.DictCursor)
        if row:
            updict = {}
            for k, v in upinfo.items():
                opdict = json.loads(row[k])
                opdict.update(v)
                updict[k] = json.dumps(opdict, ensure_ascii=False)
            up, list_up = update_dicts(updict)
            up_no = update_dicts2(inputs.data.update_no_placeholder)
            if up_no:
                up = up + "," + up_no
            up = up.lstrip(",").rstrip(",")
            sql = f"update {table_name} set {up} where {where};"
            list_up.extend(lists)
            rows = await SQLHelper.execute_commit(sql, list_up)
            return_info.status = SUCCESS
            return_info.msg_code = MsgCode.SUCCESS_CODE
            return_info.msg = "执行update sql成功,更新影响行数" + str(rows)
            return_info.data = sql
            return return_info.todict()
        else:
            return_info.status = FAILED
            return_info.msg_code = MsgCode.SQL_UPDATE_ERROR
            return_info.msg = "执行update sql失败,没有查询到待更新记录;"
            return_info.data = traceback.format_exc()
            return return_info.todict()
    except:
        return_info.status = FAILED
        return_info.msg_code = MsgCode.SQL_UPDATE_ERROR
        return_info.msg = "执行update sql失败;" + sql + ";" + "list:" + str(list_up)
        return_info.data = traceback.format_exc()
        return return_info.todict()


@router.post("/sql_api/update_control_sql")
async def update_control_sql(inputs: InputPlatformModel[UpdateSqlModelCtrl]):
    """
    用于控制库的下载
    单task_name, task_switch为1全开,为0全关
    加task_tag, task_switch为1为开, 为0为关
    :return:
    """
    return_info = ReturnInfo()
    table_name = "taskinfo_save_setting"
    task_switch = inputs.data.task_switch
    task_name = inputs.data.task_name
    task_tag = inputs.data.task_tag
    if task_switch == "1" and task_tag == "":
        sql = f"update {table_name} set task_state = 1 where task_name = '{task_name}'"
    elif task_switch == "1" and task_tag != "":
        sql = f"update {table_name} set task_state = 1, tag_state = 1 where task_name = '{task_name}' and task_tag = '{task_tag}'"
    elif task_switch == "0" and task_tag == "":
        sql = f"update {table_name} set task_state = 0 where task_name = '{task_name}'"
    elif task_switch == "0" and task_tag != "":
        sql = f"update {table_name} set task_state = 1, tag_state = 0 where task_name = '{task_name}' and task_tag = '{task_tag}'"
    else:
        return_info.status = FAILED
        return_info.msg_code = MsgCode.SQL_UPDATE_ERROR
        return_info.msg = "执行update sql失败, 因为参数传递错误"
        return_info.data = [task_name, task_tag, task_switch]
        return return_info.todict()
    try:
        await SQLHelper.execute_commit(sql)
        return_info.status = SUCCESS
        return_info.msg_code = MsgCode.SUCCESS_CODE
        return_info.msg = "执行update sql成功"
        return_info.data = sql
        return return_info.todict()
    except:
        return_info.status = FAILED
        return_info.msg_code = MsgCode.SQL_UPDATE_ERROR
        return_info.msg = "执行update sql失败;"
        return_info.data = traceback.format_exc()
        return return_info.todict()


@router.post("/cnkijournal/mark_table")
async def mark_cnki_table(input: InputPlatformModel[CnkiMarkTable]):
    '''
    level: list, issue, article
    journal_rawid: ACMS
    years_num_list: [(ACMS, 2021, 01),(ACMS, 2021, 02)]
    rawid_list: ['SKYK,2021,01,人工智能在石油勘探开发领域的应用现状与发展趋势', 'SKYK,2021,01,渤海海域莱北低凸起新近系大面积高丰度岩性油藏形成条件']
    :param input:
    :return:
    '''
    return_info = ReturnInfo()
    level = input.data.level
    if level == "list":
        journal_rawid = input.data.journal_rawid
        sql_list = "update journal_list set is_active = 0 where journal_rawid=%s and task_name='cnkijournal' and task_tag = 'cnkijournallist'"
        args = (journal_rawid,)
        await SQLHelper.execute_commit(sql_list, args)
        sql_issue = "update journal_issue set state = 2 where task_name='cnkijournal' and journal_rawid=%s and state!=5"
        await SQLHelper.execute_commit(sql_issue, args)
        sql_article = "update journal_article set is_true=2 where task_name='cnkijournal' and rawid like '%s,%' and is_true !=5"
        await SQLHelper.execute_commit(sql_article, args)
        return_info.status = SUCCESS
        return_info.msg_code = MsgCode.SUCCESS_CODE
        return_info.msg = "list及以下, 下载标志已置为2"

    elif level == "issue":
        journal_rawid = input.data.journal_rawid
        years_num_list = input.data.years_num_list
        sql_issue = "update journal_issue set state = 2 where task_name='cnkijournal' and task_tag='cnkiissue' and journal_rawid=%s and pub_year=%s and num=%s and state!=5"
        await SQLHelper.executemany_commit(sql_issue, years_num_list)
        sql_article = "update journal_article set is_true=2 where task_name='cnkijournal' and task_tag='cnkiarticle' and rawid like '%s,%s,%s,%' and is_true !=5"
        await SQLHelper.executemany_commit(sql_article, years_num_list)
        return_info.status = SUCCESS
        return_info.msg_code = MsgCode.SUCCESS_CODE
        return_info.msg = "issue及以下, 下载标志已置为2"

    elif level == "article":
        journal_rawid = input.data.journal_rawid
        years_num_list = input.data.years_num_list
        rawid_list = input.data.rawid_list
        sql_article = "update journal_article set is_true=2 where task_name='cnkijournal' and task_tag='cnkiarticle' and rawid=%s and is_true!=5"
        await SQLHelper.executemany_commit(sql_article, rawid_list)
        return_info.status = SUCCESS
        return_info.msg_code = MsgCode.SUCCESS_CODE
        return_info.msg = "article, 下载标志已置为2"
    else:
        return_info.status = FAILED
        return_info.msg_code = 601
        return_info.msg = "传入参数错误, level名称错误"
    return return_info.todict()


@router.post("/qk_journal/update")
async def mark_cnki_table(input: InputPlatformModel[QkUpdateManger]):
    '''
    用于期刊调度时候执行sql语句, 理论上只有update语句
    :param input:
    :return:
    '''

    return_info = ReturnInfo()
    journal_id = input.data.journal_id
    table_name = input.data.table_name
    sql_exc = input.data.sql_exc
    sql_list = sql_exc.split(";")
    for sql in sql_list:
        await SQLHelper.execute_commit(sql)
    last_success_time = str(datetime.datetime.now())
    sql_mark = f"update qk_all_marks_control set state = 1,last_success_time='{last_success_time}' where journal_id = '{journal_id}'"
    await SQLHelper.execute_commit(sql_mark)
    return_info.status = SUCCESS
    return_info.msg_code = 200
    return_info.msg = "传入参数错误, level名称错误"
    return return_info.todict()


@router.post("/qk_journal/qk_task_setting")
async def mark_cnki_table(input: InputPlatformModel[SaveManageTaskSettingModel]):
    '''
    用于保存期刊调度信息
    :param input:
    :return:
    '''

    return_info = ReturnInfo()
    journal_id = input.data.journal_id
    table_name = input.data.table_name
    sql_exc = input.data.sql_exc
    delay_type = input.data.delay_type
    delay_time = input.data.delay_time
    counts = input.data.counts
    types = input.data.types
    state = input.data.state
    sql = 'replace into qk_all_marks_control(journal_id, table_name, sql_exc, delay_type, delay_time, counts, types, state) values(%s, %s, %s, %s, %s, %s, %s, %s)'
    args = (journal_id, table_name, sql_exc, delay_type, delay_time, counts, types, state)
    await SQLHelper.execute_commit(sql, args)

    return_info.status = SUCCESS
    return_info.msg_code = 200
    return_info.msg = "更新成功"
    return return_info.todict()


@router.post("/airflow_management/airflow_task_setting")
async def mark_cnki_table(input: InputPlatformModel[FlowAirActive]):
    '''
    用于保存airflow调度信息
    :param input:
    :return:
    '''

    return_info = ReturnInfo()
    dag_id = input.data.dag_id
    state = input.data.state
    is_active = input.data.is_active
    main_server = input.data.main_server
    is_paused = input.data.is_paused
    need_active = input.data.need_active
    task_author = input.data.task_author
    task_manager = input.data.task_manager
    failed_notice = input.data.failed_notice
    introduce = input.data.introduce
    relation = input.data.relation
    relation_dict = dict()
    if relation != "":
        relation_list = relation.split(";")
        for one in relation_list:
            relation_dict[one] = ""
    relationship = json.dumps(relation_dict, ensure_ascii=False)
    sql = 'replace into airflow_infos(dag_id, state, is_active, main_server, is_paused, need_active, task_author,task_manager,failed_notice, introduce, relation) values(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)'
    args = (
    dag_id, state, is_active, main_server, is_paused, need_active, task_author, task_manager, failed_notice, introduce, relationship)
    await SQLHelper.execute_commit(sql, args)

    return_info.status = SUCCESS
    return_info.msg_code = 200
    return_info.msg = "更新成功"
    return return_info.todict()


# @router.post("/cqvipqyreport/update_data")
# async def update_report_table(input: InputPlatformModel[UpdateReportFile]):
#     '''
#
#     :param input:
#     :return:
#     '''
#
#     return_info = ReturnInfo()
#     task_name = input.data.task_name
#     task_tag = input.data.task_tag
#     rawid = input.data.rawid
#     file_type = input.data.file_type
#     is_success = input.data.is_success
#     if is_success == "True":
#         sql = f'update qy_report_article_new set {file_type} = 1 where task_name = "{task_name}" and task_tag = "{task_tag}" and rawid = "{rawid}"'
#     else:
#         sql = f'update qy_report_article_new set {file_type} += 1 where task_name = "{task_name}" and task_tag = "{task_tag}" and rawid = "{rawid}"'
#
#     await SQLHelper.execute_commit(sql)
#
#     return_info.status = SUCCESS
#     return_info.msg_code = 200
#     return_info.msg = "更新成功"
#     return return_info.todict()